#!/usr/bin/env python3
"""
初始化会话相关的数据库表
运行：poetry run python scripts/init_conversation_db.py
"""

import sys
from pathlib import Path

# 确保项目根目录在 sys.path
ROOT = Path(__file__).resolve().parents[1]
if str(ROOT) not in sys.path:
    sys.path.insert(0, str(ROOT))

from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from app.core.database import engine, init_db


def create_conversation_tables():
    """创建会话相关的数据库表"""
    
    # SQL 语句列表
    sql_statements = [
        # 更新用户表，添加时间戳字段（分别执行，避免语法错误）
        """
        ALTER TABLE users
        ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
        """,

        """
        ALTER TABLE users
        ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
        """,
        
        # 创建会话表
        """
        CREATE TABLE IF NOT EXISTS conversations (
            id VARCHAR(36) NOT NULL COMMENT '会话ID（UUID）',
            user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
            title VARCHAR(200) NOT NULL DEFAULT '新的聊天' COMMENT '会话标题',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
            is_deleted TINYINT(1) DEFAULT 0 COMMENT '是否已删除（软删除）',
            PRIMARY KEY (id),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            INDEX idx_user_id (user_id),
            INDEX idx_created_at (created_at),
            INDEX idx_user_created (user_id, created_at)
        ) ENGINE = InnoDB
          DEFAULT CHARSET = utf8mb4
          COLLATE = utf8mb4_unicode_ci
          COMMENT = '用户会话表'
        """,
        
        # 创建消息表
        """
        CREATE TABLE IF NOT EXISTS messages (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '消息ID',
            conversation_id VARCHAR(36) NOT NULL COMMENT '会话ID',
            sender ENUM('user', 'bot', 'system') NOT NULL COMMENT '发送者类型',
            content TEXT NOT NULL COMMENT '消息内容',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            message_order INT UNSIGNED NOT NULL COMMENT '消息在会话中的顺序',
            PRIMARY KEY (id),
            FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
            INDEX idx_conversation_id (conversation_id),
            INDEX idx_conversation_order (conversation_id, message_order),
            INDEX idx_created_at (created_at)
        ) ENGINE = InnoDB
          DEFAULT CHARSET = utf8mb4
          COLLATE = utf8mb4_unicode_ci
          COMMENT = '聊天消息表'
        """,
        
        # 创建会话统计表
        """
        CREATE TABLE IF NOT EXISTS conversation_stats (
            conversation_id VARCHAR(36) NOT NULL COMMENT '会话ID',
            message_count INT UNSIGNED DEFAULT 0 COMMENT '消息总数',
            last_message_at TIMESTAMP NULL COMMENT '最后一条消息时间',
            PRIMARY KEY (conversation_id),
            FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
        ) ENGINE = InnoDB
          DEFAULT CHARSET = utf8mb4
          COLLATE = utf8mb4_unicode_ci
          COMMENT = '会话统计表'
        """
    ]
    
    # 触发器SQL（需要单独处理）
    drop_trigger_sql = "DROP TRIGGER IF EXISTS update_conversation_stats_on_insert"

    create_trigger_sql = """
    CREATE TRIGGER update_conversation_stats_on_insert
    AFTER INSERT ON messages
    FOR EACH ROW
    BEGIN
        INSERT INTO conversation_stats (conversation_id, message_count, last_message_at)
        VALUES (NEW.conversation_id, 1, NEW.created_at)
        ON DUPLICATE KEY UPDATE
            message_count = message_count + 1,
            last_message_at = NEW.created_at;
    END
    """
    
    try:
        with engine.connect() as conn:
            print("🚀 开始创建会话相关数据库表...")
            
            # 执行表创建语句
            for i, sql in enumerate(sql_statements, 1):
                try:
                    conn.execute(text(sql))
                    print(f"  ✅ 步骤 {i}: 执行成功")
                except SQLAlchemyError as e:
                    error_msg = str(e)
                    print(f"  ⚠️  步骤 {i}: {error_msg}")
                    # 某些ALTER语句可能因为字段已存在而失败，这是正常的
                    if ("Duplicate column name" not in error_msg and
                        "already exists" not in error_msg and
                        i <= 2):  # 前两个是ALTER语句，可能失败
                        raise
            
            # 执行触发器创建
            try:
                conn.execute(text(drop_trigger_sql))
                conn.execute(text(create_trigger_sql))
                print("  ✅ 触发器创建成功")
            except SQLAlchemyError as e:
                print(f"  ⚠️  触发器创建失败: {e}")
            
            conn.commit()
            print("✅ 所有会话相关表创建完成！")
            
    except SQLAlchemyError as e:
        print(f"❌ 数据库操作失败: {e}")
        return False
    
    return True


def verify_tables():
    """验证表是否创建成功"""
    print("\n🔍 验证表结构...")
    
    tables_to_check = ['users', 'conversations', 'messages', 'conversation_stats']
    
    try:
        with engine.connect() as conn:
            for table in tables_to_check:
                result = conn.execute(text(f"SHOW TABLES LIKE '{table}'"))
                if result.fetchone():
                    print(f"  ✅ 表 {table} 存在")
                    
                    # 显示表结构
                    desc_result = conn.execute(text(f"DESCRIBE {table}"))
                    columns = desc_result.fetchall()
                    print(f"     字段数: {len(columns)}")
                else:
                    print(f"  ❌ 表 {table} 不存在")
                    
    except SQLAlchemyError as e:
        print(f"❌ 验证失败: {e}")


def main():
    """主函数"""
    print("=" * 50)
    print("🗄️  会话数据库初始化脚本")
    print("=" * 50)
    
    # 首先确保基础表存在
    print("📋 初始化基础数据库结构...")
    init_db()
    
    # 创建会话相关表
    if create_conversation_tables():
        verify_tables()
        print("\n🎉 数据库初始化完成！")
        print("💡 现在可以开始使用持久化的会话历史功能了。")
    else:
        print("\n❌ 数据库初始化失败！")
        sys.exit(1)


if __name__ == "__main__":
    main()
